package vn.keho.module.dompeto.sqlite;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;
import java.util.List;

import vn.keho.module.dompeto.model.ChannelModel;
import vn.keho.module.dompeto.model.PageModel;
import vn.keho.module.dompeto.model.ThemeModel;
import vn.keho.module.dompeto.model.WalletProfile;

/**
 * Created by KeHo on 1/19/15.
 */
public class DompetoSqliteHelper extends SQLiteOpenHelper {
    public static final String PAGE_TABLE_NAME = "Page";
    public static final String PAGE_COLUMN_PAGE_ID = "pageId";
    public static final String PAGE_COLUMN_USERNAME = "username";
    public static final String PAGE_COLUMN_TITLE = "title";

    public static final String CHANNEL_TABLE_NAME = "Channel";
    public static final String CHANNEL_COLUMN_ID = "Id";
    public static final String CHANNEL_COLUMN_CHANNEL_ID = "channelId";
    public static final String CHANNEL_COLUMN_USERNAME = "username";
    public static final String CHANNEL_COLUMN_PAGE_ID = "pageId";
    public static final String CHANNEL_COLUMN_TITLE = "title";
    public static final String CHANNEL_COLUMN_IMAGE = "image";
    public static final String CHANNEL_COLUMN_SLOT = "slot";
    public static final String CHANNEL_COLUMN_BLACK = "black";

    public static final String THEME_TABLE_NAME = "Theme";
    public static final String THEME_COLUMN_THEME_ID = "themeId";
    public static final String THEME_COLUMN_USERNAME = "username";
    public static final String THEME_COLUMN_SYNC = "sync";

    private static final String TEXT_TYPE = " TEXT";
    private static final String INTEGER_TYPE = " INTEGER";
    private static final String COMMA_SEP = ",";

    private static final String SQL_CREATE_THEME_ENTRIES =
            "CREATE TABLE " + THEME_TABLE_NAME + " (" +
                    THEME_COLUMN_THEME_ID + INTEGER_TYPE + " PRIMARY KEY" + COMMA_SEP +
                    THEME_COLUMN_USERNAME + TEXT_TYPE + COMMA_SEP +
                    THEME_COLUMN_SYNC + INTEGER_TYPE +
                    " )";

    private static final String SQL_DELETE_THEME_ENTRIES =
            "DROP TABLE IF EXISTS " + THEME_TABLE_NAME;

    //Page
    private static final String SQL_CREATE_PAGE_ENTRIES =
            "CREATE TABLE " + PAGE_TABLE_NAME + " (" +
                    PAGE_COLUMN_PAGE_ID + INTEGER_TYPE + " PRIMARY KEY" + COMMA_SEP +
                    PAGE_COLUMN_USERNAME + TEXT_TYPE + COMMA_SEP +
                    PAGE_COLUMN_TITLE + TEXT_TYPE +
                    " )";

    private static final String SQL_DELETE_PAGE_ENTRIES =
            "DROP TABLE IF EXISTS " + PAGE_TABLE_NAME;

    //Channel
    private static final String SQL_CREATE_CHANNEL_ENTRIES =
            "CREATE TABLE " + CHANNEL_TABLE_NAME + " (" +
                    CHANNEL_COLUMN_ID + INTEGER_TYPE + " PRIMARY KEY AUTOINCREMENT" + COMMA_SEP +
                    CHANNEL_COLUMN_CHANNEL_ID + TEXT_TYPE + COMMA_SEP +
                    CHANNEL_COLUMN_USERNAME + TEXT_TYPE + COMMA_SEP +
                    CHANNEL_COLUMN_PAGE_ID + INTEGER_TYPE + COMMA_SEP +
                    CHANNEL_COLUMN_TITLE + TEXT_TYPE + COMMA_SEP +
                    CHANNEL_COLUMN_IMAGE + TEXT_TYPE + COMMA_SEP +
                    CHANNEL_COLUMN_SLOT + INTEGER_TYPE + COMMA_SEP +
                    CHANNEL_COLUMN_BLACK + INTEGER_TYPE +
                    " )";

    private static final String SQL_DELETE_CHANNEL_ENTRIES =
            "DROP TABLE IF EXISTS " + CHANNEL_TABLE_NAME;


    // If you change the database schema, you must increment the database version.
    public static final int DATABASE_VERSION = 4;
    public static final String DATABASE_NAME = "Dompeto.db";

    public DompetoSqliteHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(SQL_CREATE_THEME_ENTRIES);
        db.execSQL(SQL_CREATE_PAGE_ENTRIES);
        db.execSQL(SQL_CREATE_CHANNEL_ENTRIES);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // This database is only a cache for online data, so its upgrade policy is
        // to simply to discard the data and start over
        db.execSQL(SQL_DELETE_THEME_ENTRIES);
        db.execSQL(SQL_DELETE_PAGE_ENTRIES);
        db.execSQL(SQL_DELETE_CHANNEL_ENTRIES);
        onCreate(db);
    }

    @Override
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        onUpgrade(db, oldVersion, newVersion);
    }

    public void clearTable(String username) {
        SQLiteDatabase db = getReadableDatabase();

        // Define 'where' part of query.
        String selection = PAGE_COLUMN_USERNAME + " = ?";
        // Specify arguments in placeholder order.
        String[] selectionArgs = {username};

        db.delete(
                THEME_TABLE_NAME,  // The table to query
                selection,         // The columns for the WHERE clause
                selectionArgs
        );

        db.delete(
                PAGE_TABLE_NAME,  // The table to query
                selection,         // The columns for the WHERE clause
                selectionArgs
        );

        db.delete(
                CHANNEL_TABLE_NAME,  // The table to query
                selection,         // The columns for the WHERE clause
                selectionArgs
        );
    }

    public void insertTheme(String username, int themeId, int sync) {
        ThemeModel themeModel = getTheme(username);
        // Gets the data repository in write mode
        SQLiteDatabase db = getWritableDatabase();
        // Create a new map of values, where column names are the keys
        ContentValues values = new ContentValues();
        values.put(THEME_COLUMN_THEME_ID, themeId);
        values.put(THEME_COLUMN_USERNAME, username);
        values.put(THEME_COLUMN_SYNC, sync);
        if(themeModel == null) {
            // Insert the new row, returning the primary key value of the new row
            db.insert(
                    THEME_TABLE_NAME,
                    "",
                    values);
        } else {
            // Define 'where' part of query.
            String selection = THEME_COLUMN_USERNAME + " = ?";
            // Specify arguments in placeholder order.
            String[] selectionArgs = {username};
            db.update(THEME_TABLE_NAME, values, selection, selectionArgs);
        }
    }

    public ThemeModel getTheme(String username) {
        SQLiteDatabase db = getReadableDatabase();

        // Define a projection that specifies which columns from the database
        // you will actually use after this query.
        String[] projection = {
                THEME_COLUMN_THEME_ID,
                THEME_COLUMN_USERNAME,
                THEME_COLUMN_SYNC,
        };

        // Define 'where' part of query.
        String selection = THEME_COLUMN_USERNAME + " = ?";
        // Specify arguments in placeholder order.
        String[] selectionArgs = {username};

        // How you want the results sorted in the resulting Cursor
        String sortOrder =
                THEME_COLUMN_THEME_ID + " DESC";

        Cursor cursor = db.query(
                THEME_TABLE_NAME,  // The table to query
                projection,                               // The columns to return
                selection,                                // The columns for the WHERE clause
                selectionArgs,                            // The values for the WHERE clause
                null,                                     // don't group the rows
                null,                                     // don't filter by row groups
                sortOrder                                 // The sort order
        );


        cursor.moveToFirst();
        if(cursor.getCount() > 0) {
            do {
                int themeId = cursor.getInt(0);
                username = cursor.getString(1);
                int sync = cursor.getInt(2);
                return new ThemeModel(themeId, username, sync);
            } while (cursor.moveToNext());
        }
        return null;
    }

    public void insertPage(String username, PageModel pageModel) {
        // Gets the data repository in write mode
        SQLiteDatabase db = getWritableDatabase();
        // Create a new map of values, where column names are the keys
        ContentValues values = new ContentValues();
        values.put(PAGE_COLUMN_PAGE_ID, pageModel.getPageid());
        values.put(PAGE_COLUMN_USERNAME, username);
        values.put(PAGE_COLUMN_TITLE, pageModel.getTitle());
        // Insert the new row, returning the primary key value of the new row
        db.insert(
                PAGE_TABLE_NAME,
                "",
                values);
    }

    public void insertChannel(String username, ChannelModel channelModel) {
        // Gets the data repository in write mode
        SQLiteDatabase db = getWritableDatabase();
        // Create a new map of values, where column names are the keys
        ContentValues values = new ContentValues();
        values.put(CHANNEL_COLUMN_CHANNEL_ID, channelModel.getChannelid());
        values.put(CHANNEL_COLUMN_PAGE_ID, 0);
        values.put(CHANNEL_COLUMN_USERNAME, username);
        values.put(CHANNEL_COLUMN_TITLE, channelModel.getChanneltitle());
        values.put(CHANNEL_COLUMN_IMAGE, channelModel.getImageurl());
        values.put(CHANNEL_COLUMN_SLOT, channelModel.getSlotid());
        values.put(CHANNEL_COLUMN_BLACK, 0);
        // Insert the new row, returning the primary key value of the new row
        db.insert(
                CHANNEL_TABLE_NAME,
                "",
                values);
    }

    public void removeChannel(String username, String channelId) {
        SQLiteDatabase db = getReadableDatabase();

        // Define 'where' part of query.
        String selection = CHANNEL_COLUMN_USERNAME + " = ? AND " + CHANNEL_COLUMN_CHANNEL_ID + " = ?";
        // Specify arguments in placeholder order.
        String[] selectionArgs = {username, channelId};

        db.delete(
                CHANNEL_TABLE_NAME,  // The table to query
                selection,                                // The columns for the WHERE clause
                selectionArgs                            // The values for the WHERE clause
        );



    }

    public List<ChannelModel> getListChannel(String username) {
        SQLiteDatabase db = getReadableDatabase();

        // Define a projection that specifies which columns from the database
        // you will actually use after this query.
        String[] projection = {
                CHANNEL_COLUMN_CHANNEL_ID,
                CHANNEL_COLUMN_PAGE_ID,
                CHANNEL_COLUMN_USERNAME,
                CHANNEL_COLUMN_TITLE,
                CHANNEL_COLUMN_IMAGE,
                CHANNEL_COLUMN_SLOT,
                CHANNEL_COLUMN_BLACK
        };

        // Define 'where' part of query.
        String selection = CHANNEL_COLUMN_USERNAME + " = ?";
        // Specify arguments in placeholder order.
        String[] selectionArgs = {username};

        // How you want the results sorted in the resulting Cursor
        String sortOrder =
                CHANNEL_COLUMN_CHANNEL_ID + " ASC";

        Cursor cursor = db.query(
                CHANNEL_TABLE_NAME,  // The table to query
                projection,                               // The columns to return
                selection,                                // The columns for the WHERE clause
                selectionArgs,                            // The values for the WHERE clause
                null,                                     // don't group the rows
                null,                                     // don't filter by row groups
                sortOrder                                 // The sort order
        );

        List<ChannelModel> listResult = new ArrayList<ChannelModel>();
        cursor.moveToFirst();
        if(cursor.getCount() > 0) {
            do {
                String channelId = cursor.getString(0);
                username = cursor.getString(2);
                String title = cursor.getString(3);
                String image = cursor.getString(4);
                int slot = cursor.getInt(5);
                int black = cursor.getInt(6);

                ChannelModel channelModel = new ChannelModel(channelId, title, image, slot);
                listResult.add(channelModel);
            } while (cursor.moveToNext());
        }
        return listResult;
    }

}
